﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using DTO;


namespace DataAccessTier
{
    public class HangHoaDAO:DBConnection
    {
        public HangHoaDAO() : base() { }

        //DataTable hàng hóa
        public DataTable GetAllHangHoa()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM HANGHOA";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //DataTable nhà xuất bản điều kiện mã nhà xuất bản khác "null"
        public DataTable GetAllNhaXuatBan()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlCommand cmd = new SqlCommand(" SELECT * FROM NHAXUATBAN WHERE MANXB!='"+ "null" +"'", conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //Lấy tên nhà xuất bản theo mã
        public DataSet GetTenNhaXuatBanTheoMa(string manxb)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT TENNXB FROM NHAXUATBAN WHERE MANXB='" + manxb + "'", conn);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                conn.Close();
                return dataSet;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //DataTable loại hàng hóa điều kiện mã loại khác "null"
        public DataTable GetAllLoaiHangHoa()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM LOAIHANGHOA WHERE MALOAI!='" + "null" + "'", conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //Lấy tên loại hàng theo mã loại
        public DataSet GetTenLoaiHangHoaTheoMa(string maloai)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT TENLOAI FROM LOAIHANGHOA WHERE MALOAI='" + maloai + "'", conn);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                conn.Close();
                return dataSet;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //DataTable nhà sản xuất điều kiện mã nhà sản xuất khác "null"
        public DataTable GetAllNhaSanXuat()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlCommand cmd = new SqlCommand(" SELECT * FROM NHASANXUAT WHERE MANSX!='" + "null" + "'", conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //Lấy tên nhà sản xuất theo mã
        public DataSet GetTenNhaSanXuatTheoMa(string mansx)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT TENNSX FROM NHASANXUAT WHERE MANSX='" + mansx + "'", conn);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                conn.Close();
                return dataSet;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        public bool kiemtratontai(string mahang)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM HANGHOA WHERE MAHANG = '" + mahang + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        public bool Insert(HangHoa hh)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "INSERT INTO HANGHOA(MAHANG,TENHANG,SOLUONGTRUNGBAY,SOLUONGTRONGKHO,SOLUONGTON,TACGIA,GIATHANH,MANXB,MALOAI,MANSX) VALUES('" + hh.MAHANG + "',N'" + hh.TENHANG + "','" + hh.SOLUONGTRUNGBAY + "','" + hh.SOLUONGTRONGKHO + "','" + hh.SOLUONGTON + "','" + hh.TACGIA + "','" + hh.GIATHANH + "','" + hh.MANXB + "','" + hh.MALOAI + "','" + hh.MANSX + "')";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {  
                conn.Close();
                return false;
            }
        }

        public bool Delete(string mahang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "DELETE FROM HANGHOA WHERE MAHANG = '" + mahang + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        //Kiểm tra tồn tại mã hàng bên bảng CHITIETHANGCC
        public bool kiemtratontaiHH(string mahang)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM CHITIETHANGCC WHERE MAHANG = '" + mahang + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        //Xóa mã hàng bên bảng CHITIETHANGCC
        public bool DeleteHH(string mahang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "DELETE FROM CHITIETHANGCC WHERE MAHANG = '" + mahang + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        public bool Update(HangHoa hh)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "UPDATE HANGHOA SET MAHANG = '" + hh.MAHANG + "', TENHANG = N'" + hh.TENHANG + "', SOLUONGTRUNGBAY = '" + hh.SOLUONGTRUNGBAY + "', SOLUONGTRONGKHO = '" + hh.SOLUONGTRONGKHO + "', SOLUONGTON = '" + hh.SOLUONGTON + "', TACGIA = N'" + hh.TACGIA + "', MANXB = '" + hh.MANXB + "', MALOAI = '" + hh.MALOAI + "', MANSX = '" + hh.MANSX + "', GIATHANH = '" + hh.GIATHANH + "' WHERE MAHANG = '" + hh.MAHANG + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        //Get CHITIETHANGCC có điều kiện là mã hàng
        public DataTable GetAllHangCC(string mahang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM CHITIETHANGCC WHERE MAHANG = '" + mahang + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //Update hàng hóa bên bảng CHITIETHANGCC
        public bool UpdateHangCC(int stt, string mancc, string mahang, string tenhang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "UPDATE CHITIETHANGCC SET STT = '" + stt + "', MANCC = '" + mancc + "', MAHANG = '" + mahang + "', TENHANG = N'" + tenhang + "' WHERE MAHANG = '" + mahang + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        public bool kiemtratontaimahang(string mahang)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM HANGHOA WHERE MAHANG LIKE N'" +"%"+ @mahang + "%"+"'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        public bool kiemtratontaitenhang(string tenhang)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM HANGHOA WHERE TENHANG LIKE N'" + "%" + @tenhang + "%" + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        public bool kiemtratontaitacgia(string tacgia)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM HANGHOA WHERE TACGIA LIKE N'" + "%" + @tacgia + "%" + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        //DataTable hàng hóa theo mã hàng hóa
        public DataTable GetAllHangHoaTheoMa(string mahang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM HANGHOA WHERE MAHANG LIKE N'" + "%" + @mahang + "%" + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //DataTable hàng hóa theo tên hàng hóa
        public DataTable GetAllHangHoaTheoTenHang(string tenhang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM HANGHOA WHERE TENHANG LIKE N'" + "%" + @tenhang + "%" + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //DataTable hàng hóa theo tác giả
        public DataTable GetAllHangHoaTheoTacGia(string tacgia)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM HANGHOA WHERE TACGIA LIKE N'" + "%" + @tacgia + "%" + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }
    }
}
